package hospital.daoImpl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;

import hospital.dao.StaffDao;
import hospital.model.AppointmentModel;
import hospital.model.DocScheduleModel;
import hospital.model.EmpInfoModel;
import hospital.model.LoginModel;
import hospital.model.PatientInfoModel;
import hospital.model.RecordDetailModel;
import hospital.model.UserInfoModel;
import hospital.util.HospitalUtil;

public class StaffDaoImpl implements StaffDao {
	private static final Logger log = Logger.getLogger(StaffDaoImpl.class);
	private SessionFactory sessionFactory;
	private HospitalUtil hospitalUtil;

	public HospitalUtil getHospitalUtil() {
		return hospitalUtil;
	}

	public void setHospitalUtil(HospitalUtil hospitalUtil) {
		this.hospitalUtil = hospitalUtil;
	}

	@Override
	public boolean insertCreatePatient(LoginModel loginInfo, PatientInfoModel patientInfo) {
		try {
			sessionFactory.getCurrentSession().save(loginInfo);
			sessionFactory.getCurrentSession().save(patientInfo);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public List<PatientInfoModel> selectGetPatientResult(long userId, String firstname, String lastname) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(PatientInfoModel.class, "patientInfo");
		RequestAttributes ra = RequestContextHolder.getRequestAttributes();
		long currentUserId = (long) ra.getAttribute("currentUserId", RequestAttributes.SCOPE_SESSION);
		cr.createAlias("patientInfo.grantedStaffs", "grantedStaffs");
		cr.add(Restrictions.eq("grantedStaffs.userId", currentUserId));
		if (userId != -1) {
			cr.add(Restrictions.eq("patientInfo.userId", userId));
		}
		if (!firstname.equals("")) {
			cr.add(Restrictions.eq("patientInfo.firstname", firstname));
		}
		if (!lastname.equals("")) {
			cr.add(Restrictions.eq("patientInfo.lastname", lastname));
		}
		cr.addOrder(Order.asc("patientInfo.userId"));
		try {
			return (List<PatientInfoModel>) cr.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public EmpInfoModel selectGetDefaultDoctor(long doctorId) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(EmpInfoModel.class);
		cr.add(Restrictions.eq("userId", doctorId));
		cr.add(Restrictions.eq("role", (byte) 1));
		try {
			return (EmpInfoModel) cr.uniqueResult();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<PatientInfoModel> selectGetAppointPatientResult(long userId, String firstname, String lastname) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(PatientInfoModel.class);
		if (userId != -1) {
			cr.add(Restrictions.eq("userId", userId));
		}
		if (!firstname.equals("")) {
			cr.add(Restrictions.eq("firstname", firstname));
		}
		if (!lastname.equals("")) {
			cr.add(Restrictions.eq("lastname", lastname));
		}
		cr.addOrder(Order.asc("userId"));
		try {
			return (List<PatientInfoModel>) cr.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<EmpInfoModel> selectGetAppointDocResult(long userId, String firstname, String lastname) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(EmpInfoModel.class);
		cr.add(Restrictions.eq("role", (byte) 1));
		if (userId != -1) {
			cr.add(Restrictions.eq("userId", userId));
		}
		if (!firstname.equals("")) {
			cr.add(Restrictions.eq("firstname", firstname));
		}
		if (!lastname.equals("")) {
			cr.add(Restrictions.eq("lastname", lastname));
		}
		cr.addOrder(Order.asc("userId"));
		try {
			return (List<EmpInfoModel>) cr.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<DocScheduleModel> selectGetDocSchedules(long docId) {
		try {
			String sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
					+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
					+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
					+ "(SELECT * FROM APPOINTMENT WHERE DOC_ID= :docId) AS A " + "INNER JOIN USER_INFO AS B "
					+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS E "
					+ "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F " + "ON D.STAFF_ID=F.USER_ID "
					+ "ORDER BY APPOINTMENT_TIME";
			SQLQuery sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId",
					docId));
			sqlQuery.addScalar("APPOINTMENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("DOC_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("STAFF_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("APPOINTMENT_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("PATIENT_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("STAFF_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("STAFF_LASTNAME", StandardBasicTypes.STRING);
			List<DocScheduleModel> docSchedules = new ArrayList<DocScheduleModel>();
			List<Object[]> result = (List<Object[]>) sqlQuery.list();
			Iterator<Object[]> it = result.iterator();
			while (it.hasNext()) {
				DocScheduleModel docSchedule = new DocScheduleModel();
				Object[] o = it.next();
				docSchedule.setAppointmentId((long) o[0]);
				docSchedule.setPatientId((long) o[1]);
				docSchedule.setDoctorId((long) o[2]);
				docSchedule.setStaffId((long) o[3]);
				docSchedule.setAppointmentTime((long) o[4]);
				docSchedule.setPatientName((String) o[5] + " " + (String) o[6]);
				docSchedule.setDoctorName((String) o[7] + " " + (String) o[8]);
				docSchedule.setStaffName((String) o[9] + " " + (String) o[10]);
				docSchedules.add(docSchedule);
			}
			return docSchedules;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean insertCreateAppointment(AppointmentModel appointment) {
		try {
			sessionFactory.getCurrentSession().save(appointment);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<RecordDetailModel> selectGetRecordDetailList(long patientId) {
		try {
			String sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
					+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
					+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId) AS A " + "INNER JOIN USER_INFO AS B "
					+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
					+ "ORDER BY RECORD_ID";
			SQLQuery sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
					patientId));
			sqlQuery.addScalar("RECORD_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("DOC_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("START_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("END_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("PATIENT_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DIAGNOSIS", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DRUG", StandardBasicTypes.STRING);
			sqlQuery.addScalar("INSTRUCTION", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DEA_NUM", StandardBasicTypes.STRING);
			sqlQuery.addScalar("TREATMENT_SCHE", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_COMMENT", StandardBasicTypes.STRING);
			List<RecordDetailModel> recordDetails = new ArrayList<RecordDetailModel>();
			List<Object[]> result = (List<Object[]>) sqlQuery.list();
			Iterator<Object[]> it = result.iterator();
			while (it.hasNext()) {
				RecordDetailModel recordDetail = new RecordDetailModel();
				Object[] o = it.next();
				recordDetail.setRecordId((long) o[0]);
				recordDetail.setPatientId((long) o[1]);
				recordDetail.setDoctorId((long) o[2]);
				recordDetail.setStartTime((long) o[3]);
				recordDetail.setEndTime((long) o[4]);
				recordDetail.setPatientName((String) o[5] + " " + (String) o[6]);
				recordDetail.setDoctorName((String) o[7] + " " + (String) o[8]);
				recordDetail.setDiagnosis((String) o[9]);
				recordDetail.setDrug((String) o[10]);
				recordDetail.setInstruction((String) o[11]);
				recordDetail.setDeaNum((String) o[12]);
				recordDetail.setTreatmentSchedule((String) o[13]);
				recordDetail.setComment((String) o[14]);
				recordDetails.add(recordDetail);
			}
			return recordDetails;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<DocScheduleModel> selectGetAppointmentList(long appointmentId, long patientId, long doctorId) {
		try {
			String sql = "";
			SQLQuery sqlQuery = null;
			int count = 0;
			if (appointmentId != -1) {
				count += 1;
			}
			if (patientId != -1) {
				count += 2;
			}
			if (doctorId != -1) {
				count += 4;
			}
			switch (count) {
			case 0:
				return null;
			case 1:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE APPOINTMENT_ID= :appointmentId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS E " + "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F "
						+ "ON D.STAFF_ID=F.USER_ID " + "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("appointmentId",
						appointmentId));
				break;
			case 2:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE PATIENT_ID= :patientId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS E " + "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F "
						+ "ON D.STAFF_ID=F.USER_ID " + "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 3:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE APPOINTMENT_ID= :appointmentId AND PATIENT_ID= :patientId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS E " + "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F "
						+ "ON D.STAFF_ID=F.USER_ID " + "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("appointmentId", appointmentId).setLong("patientId", patientId));
				break;
			case 4:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE DOC_ID= :docId) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS E "
						+ "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F " + "ON D.STAFF_ID=F.USER_ID "
						+ "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("docId", doctorId));
				break;
			case 5:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE APPOINTMENT_ID= :appointmentId AND DOC_ID= :docId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS E " + "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F "
						+ "ON D.STAFF_ID=F.USER_ID " + "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("appointmentId", appointmentId).setLong("docId", doctorId));
				break;
			case 6:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE PATIENT_ID= :patientId AND DOC_ID= :docId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS E " + "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F "
						+ "ON D.STAFF_ID=F.USER_ID " + "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("docId", doctorId));
				break;
			case 7:
				sql = "SELECT D.*, F.FIRST_NAME AS STAFF_FIRSTNAME, F.LAST_NAME AS STAFF_LASTNAME FROM "
						+ "(SELECT C.*, E.FIRST_NAME AS DOC_FIRSTNAME, E.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM APPOINTMENT WHERE APPOINTMENT_ID= :appointmentId AND PATIENT_ID= :patientId AND DOC_ID= :docId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS E " + "ON C.DOC_ID=E.USER_ID) AS D " + "INNER JOIN USER_INFO AS F "
						+ "ON D.STAFF_ID=F.USER_ID " + "ORDER BY APPOINTMENT_TIME";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("appointmentId", appointmentId).setLong("patientId", patientId).setLong("docId",
						doctorId));
				break;
			default:
				break;
			}
			sqlQuery.addScalar("APPOINTMENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("DOC_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("STAFF_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("APPOINTMENT_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("PATIENT_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("STAFF_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("STAFF_LASTNAME", StandardBasicTypes.STRING);
			List<DocScheduleModel> docSchedules = new ArrayList<DocScheduleModel>();
			List<Object[]> result = (List<Object[]>) sqlQuery.list();
			Iterator<Object[]> it = result.iterator();
			while (it.hasNext()) {
				DocScheduleModel docSchedule = new DocScheduleModel();
				Object[] o = it.next();
				docSchedule.setAppointmentId((long) o[0]);
				docSchedule.setPatientId((long) o[1]);
				docSchedule.setDoctorId((long) o[2]);
				docSchedule.setStaffId((long) o[3]);
				docSchedule.setAppointmentTime((long) o[4]);
				docSchedule.setPatientName((String) o[5] + " " + (String) o[6]);
				docSchedule.setDoctorName((String) o[7] + " " + (String) o[8]);
				docSchedule.setStaffName((String) o[9] + " " + (String) o[10]);
				docSchedules.add(docSchedule);
			}
			return docSchedules;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}

	}

	@Override
	public boolean updateAppointment(AppointmentModel appointment) {
		try {
			Session session = sessionFactory.getCurrentSession();
			AppointmentModel orig = (AppointmentModel) session.get(AppointmentModel.class,
					appointment.getAppointmentId());
			orig.setPatientId(appointment.getPatientId());
			orig.setDoctorId(appointment.getDoctorId());
			orig.setStaffId(appointment.getStaffId());
			orig.setAppointmentTime(appointment.getAppointmentTime());
			session.update(orig);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean deleteAppointment(long deleteId) {
		try {
			Session session = sessionFactory.getCurrentSession();
			AppointmentModel deleteAppointment = (AppointmentModel) session.get(AppointmentModel.class, deleteId);
			session.delete(deleteAppointment);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}

	}

	@Override
	public boolean updatePatient(PatientInfoModel patientInfo) {
		try {
			Session session = sessionFactory.getCurrentSession();
			PatientInfoModel orig = (PatientInfoModel) session.get(PatientInfoModel.class, patientInfo.getUserId());
			orig.setAddress(patientInfo.getAddress());
			orig.setCellphone(patientInfo.getCellphone());
			orig.setCurrentHealth(patientInfo.getCurrentHealth());
			orig.setDateOfBirth(patientInfo.getDateOfBirth());
			orig.setFirstname(patientInfo.getFirstname());
			orig.setGender(patientInfo.getGender());
			orig.setLastname(patientInfo.getLastname());
			orig.setPhonenum(patientInfo.getPhonenum());
			orig.setPostalCode(patientInfo.getPostalCode());
			session.update(orig);
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public List<PatientInfoModel> selectGetAllPatients() {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(PatientInfoModel.class, "patientInfo");
		RequestAttributes ra = RequestContextHolder.getRequestAttributes();
		long currentUserId = (long) ra.getAttribute("currentUserId", RequestAttributes.SCOPE_SESSION);
		cr.createAlias("patientInfo.grantedStaffs", "grantedStaffs");
		cr.add(Restrictions.eq("grantedStaffs.userId", currentUserId));
		cr.addOrder(Order.asc("patientInfo.userId"));
		try {
			return (List<PatientInfoModel>) cr.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<RecordDetailModel> selectSearchRecord(long patientId, long recordTimeStart, long recordTimeEnd,
			String diagnosis, String drug, String comment) {
		try {
			String sql = "";
			SQLQuery sqlQuery = null;
			int count = 0;
			if (recordTimeStart != -1) {
				count += 1;
			}
			if (!diagnosis.equals("")) {
				count += 2;
			}
			if (!drug.equals("")) {
				count += 4;
			}
			if (!comment.equals("")) {
				count += 8;
			}
			if (recordTimeEnd != -1) {
				count += 16;
			}
			switch (count) {
			case 0:
				return selectGetRecordDetailList(patientId);

			case 1:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND START_TIME>= :recordTime) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 2:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DIAGNOSIS RLIKE '.*"
						+ diagnosis + ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 3:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DIAGNOSIS RLIKE '.*"
						+ diagnosis + ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 4:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DRUG RLIKE '.*" + drug
						+ ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 5:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DRUG RLIKE '.*" + drug
						+ ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 6:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DIAGNOSIS RLIKE '.*"
						+ diagnosis + ".*' AND DRUG RLIKE '.*" + drug + ".*') AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 7:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DRUG RLIKE '.*" + drug
						+ ".*' AND START_TIME>= :recordTime AND DIAGNOSIS RLIKE '.*" + diagnosis + ".*') AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 8:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 9:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 10:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis + ".*') AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 11:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis
						+ ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 12:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*') AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 13:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*' AND START_TIME>= :recordTime) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 14:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis
						+ ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("patientId",
						patientId));
				break;
			case 15:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis
						+ ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTime", recordTimeStart));
				break;
			case 16:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 17:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND START_TIME>= :recordTime) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 18:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DIAGNOSIS RLIKE '.*"
						+ diagnosis + ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 19:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DIAGNOSIS RLIKE '.*"
						+ diagnosis + ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 20:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DRUG RLIKE '.*"
						+ drug + ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 21:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DRUG RLIKE '.*"
						+ drug + ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 22:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DIAGNOSIS RLIKE '.*"
						+ diagnosis + ".*' AND DRUG RLIKE '.*" + drug + ".*') AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 23:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DRUG RLIKE '.*"
						+ drug + ".*' AND START_TIME>= :recordTime AND DIAGNOSIS RLIKE '.*" + diagnosis + ".*') AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 24:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 25:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 26:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis + ".*') AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 27:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis
						+ ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 28:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*') AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 29:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*' AND START_TIME>= :recordTime) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			case 30:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis
						+ ".*') AS A " + "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID " + "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 31:
				sql = "SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD WHERE PATIENT_ID= :patientId AND END_TIME< :recordTimeEnd AND DOC_COMMENT RLIKE '.*"
						+ comment + ".*' AND DRUG RLIKE '.*" + drug + ".*' AND DIAGNOSIS RLIKE '.*" + diagnosis
						+ ".*' AND START_TIME>= :recordTime) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID "
						+ "ORDER BY RECORD_ID";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong("recordTime",
						recordTimeStart));
				break;
			default:
				break;
			}
			sqlQuery.addScalar("RECORD_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("DOC_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("START_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("END_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("PATIENT_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DIAGNOSIS", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DRUG", StandardBasicTypes.STRING);
			sqlQuery.addScalar("INSTRUCTION", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DEA_NUM", StandardBasicTypes.STRING);
			sqlQuery.addScalar("TREATMENT_SCHE", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_COMMENT", StandardBasicTypes.STRING);
			List<RecordDetailModel> recordDetails = new ArrayList<RecordDetailModel>();
			List<Object[]> result = (List<Object[]>) sqlQuery.list();
			Iterator<Object[]> it = result.iterator();
			while (it.hasNext()) {
				RecordDetailModel recordDetail = new RecordDetailModel();
				Object[] o = it.next();
				recordDetail.setRecordId((long) o[0]);
				recordDetail.setPatientId((long) o[1]);
				recordDetail.setDoctorId((long) o[2]);
				recordDetail.setStartTime((long) o[3]);
				recordDetail.setEndTime((long) o[4]);
				recordDetail.setPatientName((String) o[5] + " " + (String) o[6]);
				recordDetail.setDoctorName((String) o[7] + " " + (String) o[8]);
				recordDetail.setDiagnosis((String) o[9]);
				recordDetail.setDrug((String) o[10]);
				recordDetail.setInstruction((String) o[11]);
				recordDetail.setDeaNum((String) o[12]);
				recordDetail.setTreatmentSchedule((String) o[13]);
				recordDetail.setComment((String) o[14]);
				recordDetails.add(recordDetail);
			}
			return recordDetails;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean selectCheckAvailability(String email) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(LoginModel.class);
		cr.add(Restrictions.eq("username", email));
		try {
			if (cr.uniqueResult() == null) {
				return true;
			} else {
				return false;
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean selectCheckSinUnique(String sin) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(UserInfoModel.class);
		cr.add(Restrictions.eq("sin", sin));
		try {
			if (cr.uniqueResult() == null) {
				return true;
			} else {
				return false;
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean selectCheckPatientExist(long patientId) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(PatientInfoModel.class);
		cr.add(Restrictions.eq("userId", patientId));
		try {
			if (cr.uniqueResult() == null) {
				return false;
			} else {
				return true;
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean selectCheckOhipUnique(String ohip) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(PatientInfoModel.class);
		cr.add(Restrictions.eq("ohip", ohip));
		try {
			if (cr.uniqueResult() == null) {
				return true;
			} else {
				return false;
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}
	}

	@Override
	public boolean updateUsername(String email, long patientId) {
		try {
			String sql = "UPDATE LOGIN SET USERNAME= :email WHERE USERNAME=(SELECT EMAIL FROM USER_INFO WHERE USER_ID= :patientId)";
			SQLQuery sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql)
					.setString("email", email).setLong("patientId", patientId));
			sqlQuery.executeUpdate();
			return true;
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}

	}

	@Override
	public String selectCheckPassword() {
		try {
			RequestAttributes ra = RequestContextHolder.getRequestAttributes();
			long currentUserId = (long) ra.getAttribute("currentUserId", RequestAttributes.SCOPE_SESSION);
			String sql = "SELECT B.PASSWORD FROM " + "(SELECT * FROM USER_INFO WHERE USER_ID= :userId) AS A "
					+ "INNER JOIN LOGIN AS B " + "ON A.EMAIL=B.USERNAME";
			SQLQuery sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("userId",
					currentUserId));
			sqlQuery.addScalar("PASSWORD", StandardBasicTypes.STRING);
			return (String) sqlQuery.uniqueResult();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean updateChangePassword(String password) {
		try {
			Session session = sessionFactory.getCurrentSession();
			Criteria cr = session.createCriteria(EmpInfoModel.class);
			RequestAttributes ra = RequestContextHolder.getRequestAttributes();
			long currentUserId = (long) ra.getAttribute("currentUserId", RequestAttributes.SCOPE_SESSION);
			cr.add(Restrictions.eq("userId", currentUserId));

			if (cr.uniqueResult() != null) {
				EmpInfoModel empInfo = (EmpInfoModel) cr.uniqueResult();
				LoginModel login = (LoginModel) session.get(LoginModel.class, empInfo.getEmail());
				login.setPassword(hospitalUtil.encrypt(password));
				session.update(login);
				return true;
			} else {
				return false;
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}
	}
}
